<?php
	# File: datab_api.php
	# Last modification: 28.04.2011
	# Author(s): kivio.wanderley
	# 
	# Database functions.
?>

<?php
	
	$t_class_dir = dirname( __FILE__ ).DIRECTORY_SEPARATOR.'class'.DIRECTORY_SEPARATOR;
	require_once($t_class_dir . 'connection_cl.php');

	/**
	 * Creates a connection to the database.
	 * @param (string) $p_hostname Host of the database;
	 * @param (string) $p_port The port to connect to database;
	 * @param (string) $p_database_name Database name; 
	 * @param (string) $p_username User name for database;
	 * @param (string) $p_password Password for database;
	 * @return True if its successful or an erro.
	 */
	function datab_connect($p_hostname = null, $p_port = null, $p_database_name = null, $p_username = null, $p_password = null) {
		global $g_dbcon;
		
		if( ! datab_is_connected() ) {
			/* Try connection */
			try {
				$g_dbcon = new class_conexao($p_hostname, $p_port, $p_database_name, $p_username, $p_password);
				
			# Connection fail
			} catch (ConnectionException $ce) {
				//trigger_error("CONNECTION FAILURE", E_USER_ERROR);
				return false;
			}
		}
		return true;
	}
	
	/**
	 * Verify whether a connection to the database exists.
	 * @return A bool global variable.
	 */
	function datab_is_connected() {
		global $g_dbcon;

		if( !$g_dbcon || $g_dbcon->con == 0 ) {
			return false;
		} else {
			return true;
		}
		
		return true;
	}
	
	/**
	 * Executes a query.
	 * @param (string) $p_query String with the query to execute on database;
	 */
	function datab_query($p_query) {
		global $g_dbcon;

//		if( !isset($g_dbcon) ) {
//			if( isset($_SESSION['CONNEC']) ) {
//				$g_dbcon = unserialize($_SESSION['CONNEC']);
//			} 
//		}
		
		return $g_dbcon->execute_query($p_query);
	}
	
	/* ========================================
	 * Prepare vars
	 * ========================================
	 */
	
	/**
	 * Verify if value is an empty string or null.
	 * @param (string) $p_value Also can be used with numeric;
	 * @return True if is an empty string or null and false otherwise.
	 */
	function datab_prepare_isnull($p_value) {
		if(trim($p_value) == '' || $p_value == null) {
			return true;
		} else {
			return false;
		}
	}
	
	/**
	 * Prepare the string value uppercased and with quotes to use on database.
	 * @param (string) $p_field Field name to use on erro;
	 * @param (string) $p_value Value to manipulate;
	 * @return Prepared string or a NULL string if its null or empty.
	 */
	function datab_prepare_string($p_field, $p_value) {
		# If its empty of null set a db NULL value
		if(datab_prepare_isnull($p_value)) { return "NULL"; }
		# If its not a string value get show erro
		if( !is_string($p_value) ) { trigger_error("Only string allowed on ".$p_field, E_USER_ERROR); }
		
		$p_value = trim($p_value);
		$p_value = strtoupper($p_value);
		$p_value = "'".$p_value."'";
		
		return $p_value;
	}
	
	/**
	 * Returns the numeric value with only numeric chars and with quotes to use on database.
	 * @param (string) $p_field Field name to use on erro;
	 * @param (string) $p_value Value to manipulate;
	 * @return Prepared numeric or a NULL string if its null or empty.
	 */
	function datab_prepare_numeric($p_field, $p_value) {
		# If its empty of null set a db NULL value
		if(datab_prepare_isnull($p_value)) { return "NULL"; }
		# If its not a string value get show erro
		if( !is_string($p_value) ) { trigger_error("Only string allowed on ".$p_field, E_USER_ERROR); }
		
		$p_value = preg_replace("/\D/", "", $p_value); // Removes nan chars
		$p_value = "'".$p_value."'";
		
		return $p_value;
	}
	
	/**
	 * Returns the integer value with only numeric chars and no zeros on left to use on database.
	 * @param (string) $p_field Field name to use on erro;
	 * @param (string) $p_value Value to manipulate;
	 * @return Prepared integer or a NULL string if its null or empty.
	 */
	function datab_prepare_int($p_field, $p_value) {
		# If its empty of null set a db NULL value
		if(datab_prepare_isnull($p_value)) { return "NULL"; }
		# If its not a numeric value get show erro
		if( !is_numeric($p_value) ) { trigger_error("Only numeric allowed on ".$p_field, E_USER_ERROR); }
		
		if( !is_int($p_value) ) {
			$p_value = preg_replace("/\D/", "", $p_value); // Removes nan chars
			$p_value = preg_replace("/^0+/", "", $p_value); // Removes zeros on the left
		}
		
		$p_value = (int)$p_value;
		
		return $p_value;
	}
	
	/**
	 * Prepare the double value to use on database.
	 * @param (string) $p_field Field name to use on erro;
	 * @param (string) $p_value Value to manipulate;
	 * @return Prepared double or a NULL string if its null or empty.
	 */
	function datab_prepare_double($p_field, $p_value) {
		# If its empty of null set a db NULL value
		if(datab_prepare_isnull($p_value)) { return "NULL"; }
		# If its not a numeric value get show erro
		$p_value = str_replace(".", "", $p_value);
		$p_value = str_replace(",", ".", $p_value);
		if( !is_numeric($p_value) ) { trigger_error("Only numeric allowed on ".$p_field, E_USER_ERROR); }
		
		$p_value = preg_replace("/(\d+)(\d{2})$/", "$1.$2", $p_value); //Put the double point in case dont have
		$p_value = preg_replace("/\D[.]/", "", $p_value); //Accepts only digits and point
		$p_value = $p_value;
		
		return $p_value;
	}
	
	/**
 	 * Try to prepare an input of date type in a especific format for db.
	 * @param (string) $p_field Name of the field;
	 * @param (string) $p_value Date string value;
	 * @param (string) $p_format Can be 'ddmmyy', 'ddmmyyyy', 'yymmdd' or 'yyyymmdd'; 
	 * @return Data in the param format or false if it is not in.
	 */
	function datab_prepare_date($p_field, $p_value, $p_format = "ddmmyyyy") {
		# If its empty of null set a db NULL value
		if(datab_prepare_isnull($p_value)) { return "NULL"; }
		# If its not a string value get show erro
		if( !is_string($p_value) ) { trigger_error("Only string allowed on ".$p_field, E_USER_ERROR); }
		elseif( !is_string($p_format) ) { trigger_error("Only string allowed for 'format' on ".$p_field, E_USER_ERROR); }
		
		$t_arr = explode("-", $p_value);

		$p_value = preg_replace("/\D/", "", $p_value);
		
		$p_format = strtolower($p_format);
		$p_format = preg_replace("/([^dmy])/", "", $p_format); //Leave just 'd', 'm' or 'y' chars

		if(strlen($t_arr[0]) == 4 && $p_format == "yyyymmdd") {
			$regex = "/(\d{4})(\d{2})(\d{2})$/";
			if( !preg_match($regex, $p_value) ) {
				trigger_error("Wrong input value on ".$p_field, E_USER_ERROR);
			}
			$p_value = preg_replace($regex, "$1-$2-$3", $p_value);
			
		} elseif (strlen($t_arr[2]) == 4 && $p_format == "ddmmyyyy") {
			$regex = "/(\d{2})(\d{2})(\d{4})$/";
			if( !preg_match($regex, $p_value) ) {
				trigger_error("Wrong input value on ".$p_field, E_USER_ERROR);
			}
			$p_value = preg_replace($regex, "$3-$2-$1", $p_value);
			
		} elseif (strlen($t_arr[0]) == 2 && $p_format == "yymmdd") {
			$regex = "/(\d{2})(\d{2})(\d{2})$/";
			if( !preg_match($regex, $p_value) ) {
				trigger_error("Wrong input value on ".$p_field, E_USER_ERROR);
			}
			$p_value = preg_replace($regex, "$1-$2-$3", $p_value);
			
		} elseif (strlen($t_arr[0]) == 2 && $p_format == "ddmmyy") {
			$regex = "/(\d{2})(\d{2})(\d{2})$/";
			if( !preg_match($regex, $p_value) ) {
				trigger_error("Wrong input value on ".$p_field, E_USER_ERROR);
			}
			$p_value = preg_replace($regex, "$3-$2-$1", $p_value);
			
		} else {
			trigger_error("Input not in (".$p_format.") 'format', on ".$p_field, E_USER_ERROR);
			return false;
		}

		$p_value = "'".$p_value."'";
		return $p_value;
	}
	
	/* ========================================
	 * Selects
	 * ========================================
	 */
	/**
	* Select a person from people table.
	* @param (int) $p_id Id to compare on database;
	* @return Return just one row of result.
	*/
	function datab_person_select( $p_id = null ) {
		/* Select from people */
		$t_query = "SELECT id AS id, cpf AS cpf, nome AS first_name, sobrenome AS last_name
						FROM tb_usuario
						WHERE id = $p_id;";
		$t_result = datab_query($t_query);
	
		# If result got en error
		if(is_string($t_result)) {
		set_info_msg($t_result, I_ERR);
		}
	
		# If result got more than one result
		if( sizeof($t_result) > 1 ) {
		set_info_msg("MORE THAN ONE RESULT ON PERSON SELECT", I_ERR);
		}
	
		return $t_result[0];
		}
		
	/**
	 * Select all on database for the given type. 
	 * @param (string) $p_type Type to select;
	 * @return Return rows of result.
	 */
	function datab_select_all( $p_type ) {
		if( $p_type == 'people' ) $t_type = "tb_pessoa";
		else if( $p_type == 'teacher' ) $t_type = "tb_professor";
		else if( $p_type == 'student' ) $t_type = "tb_aluno";
		else if( $p_type == 'class' ) $t_type = "tb_turma";
		else { trigger_error("TYPE ERRO", E_USER_ERROR); }
		
		$t_query = "SELECT *
					FROM $t_type;";
		$t_result = datab_query($t_query);	
			
		# If result got en error
		if(is_string($t_result)) { trigger_error($t_result, E_USER_ERROR); }
		
		return $t_result;
	}
	
	/* ========================================
	 * Inserts
	 * ========================================
	 */
	/**
	* Insert a person on people table.
	* @param (string) $p_cpf Numeric string;
	* @param (string) $p_first_name First name of the person;
	* @param (string) $p_last_name Last name of the person;
	* @return Registred person Id on database.
	*/
	function datab_people_insert($p_cpf, $p_first_name, $p_last_name)
	{
		$t_query = "INSERT INTO tb_usuario (nome, sobrenome, cpf)
						
						VALUES ( $p_first_name, $p_last_name, $p_cpf );";
		$t_row = datab_query($t_query);
		if(is_string($t_row)) {
			set_info_msg($t_row, I_ERR); return false;
	}
	
	# Get and return the recent added people id
	$t_id = datab_query("SELECT LAST_INSERT_ID();");

	return $t_id[0]['LAST_INSERT_ID()'];
	}
	
	/* ========================================
	 * Misc
	 * ========================================
	 */
	
	/**
	 * Select the given type on database and post result rows.
	 * @param (string) $p_type Type to select on database;
	 */
	function datab_get_type_data( $p_type, $p_id = null ) {
		$return = array();
		$t_type = $p_type;
	
		if( $t_type == 'people') { # For people
			$people_option_list = $p_id;
			
			/* Select from people */
			$t_people_row = datab_person_select($people_option_list);
			$t_peo_id = $t_people_row['id']; //Get id for addess

			$return['people_row'] = $t_people_row;
			
		} elseif ( $t_type == 'user' ) { # For class 
			$user_option_list = $p_id;
					
			/* Select from people */
			$t_people_row = datab_person_select($user_option_list);
			$t_peo_id = $t_people_row['id']; //Get id for addess
			
			/* Select from user */
			$t_user_row = datab_user_select($user_option_list);
			
			$return['people_row'] = $t_people_row;
			$return['user_row'] = $t_user_row;
			
		} else {
			set_info_msg("NO TYPE ON datab_get_type_data()", I_ERR, config_get('uri_prefix')."?page=home_page");
		}
		
		return $return;
	}

?>